package com.modudf.persist;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.modudf.common.Constants;
import com.modudf.common.JdbcUtil;
import com.modudf.common.Page;
import com.modudf.domain.MaskInfo;
import com.modudf.exception.SystemError;
import com.modudf.exception.SystemException;

public class MaskDaoImpl implements MaskDao {

	private static final Log log = LogFactory.getLog(MaskDaoImpl.class);
	/**
	 * 单例模式
	 */
	private static MaskDao dao = new MaskDaoImpl();

	private MaskDaoImpl() {

	}

	public static MaskDao getInstance() {
		return dao;
	}

	@Override
	public void addMaskInfo(MaskInfo maskInfo) {
		Connection conn = JdbcUtil.getConnection();
		String sql = "insert into maskinfo(mid,owner,tab_name,tab_column,func_name,func_param,func_comment) values(mask_seq.nextval,?,?,?,?,?,?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, maskInfo.getOwner());
			ps.setString(2, maskInfo.getTabName());
			ps.setString(3, maskInfo.getTabColumn());
			ps.setString(4, maskInfo.getFuncName());
			ps.setString(5, maskInfo.getFuncParam());
			ps.setString(6, maskInfo.getFuncComment());
			ps.executeUpdate();
			log.debug(sql);
		} catch (SQLException e) {
			JdbcUtil.rollbackConnection(conn);
			throw new SystemException("sql语句出错");
		} catch (Exception e) {
			if (e.toString().indexOf("SystemException") != -1) {
				throw new SystemException(e.getMessage());
			} else {
				JdbcUtil.rollbackConnection(conn);
				throw new SystemError("数据库访问失败");
			}
		} finally {
			JdbcUtil.closeConnection(conn);
		}
	}

	@Override
	public Page findAllMaskInfo(String keyword, int pageIndex) {
		Connection conn = JdbcUtil.getConnection();
		int from = (pageIndex - 1) * Constants.PAGE_SIZE + 1;
		int to = pageIndex * Constants.PAGE_SIZE;
		String sql = querySql(keyword, from, to);
		String countSql = countSql(keyword);
		log.debug(sql);
		log.debug(countSql);
		Page p = new Page();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				MaskInfo m = new MaskInfo();
				m.setMid(rs.getInt("mid"));
				m.setOwner(rs.getString("owner"));
				m.setTabName(rs.getString("tab_name"));
				m.setTabColumn(rs.getString("tab_column"));
				m.setFuncName(rs.getString("func_name"));
				m.setFuncParam(rs.getString("func_param"));
				m.setFuncComment(rs.getString("func_comment"));
				p.getResult().add(m);
			}
			if (p.getResult().size() > 0) {
				PreparedStatement ps2 = conn
						.prepareStatement(countSql);
				ResultSet rs2 = ps2.executeQuery();
				int countTemp = -1;
				while (rs2.next()) {
					countTemp = rs2.getInt("count");
				}
				if (countTemp != -1) {
					int totalPage = 0;
					if (countTemp % Constants.PAGE_SIZE == 0) {
						totalPage = countTemp / Constants.PAGE_SIZE;
					} else {
						totalPage = countTemp / Constants.PAGE_SIZE + 1;
					}
					p.setCountRow(countTemp);
					p.setCurrPage(pageIndex);
					p.setTotalPage(totalPage);
				}
			}
			return p;
		} catch (SQLException e) {
			JdbcUtil.rollbackConnection(conn);
			throw new SystemException("sql语句出错");
		} catch (Exception e) {
			if (e.toString().indexOf("SystemException") != -1) {
				throw new SystemException(e.getMessage());
			} else {
				JdbcUtil.rollbackConnection(conn);
				throw new SystemError("数据库访问失败");
			}
		} finally {
			JdbcUtil.closeConnection(conn);
		}
	}

	@Override
	public void deleteMaskInfo(int mid) {
		String sql="delete from maskinfo m where m.mid=?";
		Connection conn = null;
		try{
			conn = JdbcUtil.getConnection();
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, mid);
			ps.executeUpdate();
			log.debug(sql);
		}catch(SQLException e){
			throw new SystemException("sql语句出错");
		}catch(Exception e){
			throw new SystemError("数据库访问失败");
		}finally{
			JdbcUtil.closeConnection(conn);
		}
	}

	@Override
	public MaskInfo findMaskInfo(int mid) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void updateMaskInfo(MaskInfo maskInfo) {
		Connection conn=null;
		String sql="update maskinfo m set owner=?,tab_name=?,tab_column=?,func_name=?,func_param=?,func_comment=?" +
				" where mid=?";
		try{
			conn=JdbcUtil.getConnection();
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, maskInfo.getOwner());
			ps.setString(2, maskInfo.getTabName());
			ps.setString(3, maskInfo.getTabColumn());
			ps.setString(4, maskInfo.getFuncName());
			ps.setString(5, maskInfo.getFuncParam());
			ps.setString(6, maskInfo.getFuncComment());
			ps.setInt(7, maskInfo.getMid());
			ps.executeUpdate();
			log.debug(sql);
		}catch(SQLException e){
			JdbcUtil.rollbackConnection(conn);
			throw new SystemException("sql语句出错");
		}catch(Exception e){
			JdbcUtil.rollbackConnection(conn);
			throw new SystemError("数据库访问失败");
		}finally{
			JdbcUtil.closeConnection(conn);
		}
	}

	// 拼接查询的sql语句
	private String querySql(String keyword) {
		StringBuffer sb = new StringBuffer(
				"select rownum rn,mid,owner,tab_name,tab_column,func_name,func_param,func_comment from maskinfo m where 1=1 ");
		sb.append(conditionSql(keyword));
		return sb.toString();
	}

	// 拼接查询码内建视图的sql语句
	private String querySql(String keyword, int from, int to) {
		StringBuffer sb = new StringBuffer("select t.* from (");
		sb.append(this.querySql(keyword)).append(")t where t.rn between ")
				.append(from).append(" and ").append(to);
		return sb.toString();
	}

	// 拼接查询总条数的sql语句
	private String countSql(String keyword) {
		StringBuffer sb = new StringBuffer(
				"select count(*) count from  maskinfo m where 1=1 ");
		sb.append(conditionSql(keyword));
		return sb.toString();
	}

	private String conditionSql(String keyword) {
		if (keyword != null && keyword.length() > 0) {
			StringBuffer sb = new StringBuffer();
			sb.append(" and m.owner like '%").append(keyword).append("%'");
			sb.append(" or m.tab_name like '%").append(keyword).append("%'");
			sb.append(" or m.tab_column like '%").append(keyword).append("%'");
			sb.append(" or m.func_name like '%").append(keyword).append("%'");
			sb.append(" or m.func_name like '%").append(keyword).append("%'");
			sb.append(" or m.func_comment like '%").append(keyword)
					.append("%'");
			return sb.toString();
		}
		return "";
	}

}
